/**
 * 版权所有(C)，华子不脱发科技有限公司，2024，所有权利保留。
 * <p>
 * 项目名： shangji3
 * 文件名： CatalogDao.java
 * 模块说明：
 * 修改历史：
 * 2024年05月05日 - Hedh - 创建。
 */
package nie.dao;

import nie.bean.Catalog;
import nie.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Hedh
 * @since 1.0
 */
public class CatalogDao {

  public Catalog getById(int id) {
    Connection conn;
    Statement stmt;
    ResultSet rs;
    try {
      conn = JDBCUtils.getConnection();
      stmt = conn.createStatement();
      String sql = "SELECT * FROM t_catalog where id='" + id + "'";
      rs = stmt.executeQuery(sql);
      // 处理结果集
      while (rs.next()) {
        Catalog catalog = new Catalog();
        catalog.setId(rs.getInt("id"));
        catalog.setName(rs.getString("name"));
        return catalog;
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return null;
  }

  public Catalog getByCatalogName(String catalogName) {
    Connection conn;
    Statement stmt;
    ResultSet rs;
    try {
      conn = JDBCUtils.getConnection();
      stmt = conn.createStatement();
      String sql = "SELECT * FROM t_catalog where name='" + catalogName + "'";
      rs = stmt.executeQuery(sql);
      // 处理结果集
      while (rs.next()) {
        Catalog catalog = new Catalog();
        catalog.setId(rs.getInt("id"));
        catalog.setName(rs.getString("name"));
        return catalog;
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return null;
  }

  public List<Catalog> getAllCatalogs() {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    List<Catalog> catalogs = new ArrayList<>();
    try {
      // 获得数据的连接
      conn = JDBCUtils.getConnection();
      // 获得Statement对象
      stmt = conn.createStatement();
      // 发送SQL语句
      String sql = "SELECT * FROM t_catalog";
      rs = stmt.executeQuery(sql);
      // 处理结果集
      while (rs.next()) {
        Catalog catalog = new Catalog();
        catalog.setId(rs.getInt("id"));
        catalog.setName(rs.getString("name"));
        catalogs.add(catalog);
      }
      return catalogs;
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtils.release(rs, stmt, conn);
    }
    return new ArrayList<>();
  }

  public boolean insert(Catalog catalog) {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
      // 获得数据的连接
      conn = JDBCUtils.getConnection();

      // 发送SQL语句
      String sql = "insert into t_catalog(name) values(?)";

      ps = conn.prepareStatement(sql);
      ps.setString(1, catalog.getName());
      int num = ps.executeUpdate();
      return num > 0;
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtils.release(rs, ps, conn);
    }
    return false;
  }

  public int updateById(Catalog catalog) {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
      conn = JDBCUtils.getConnection();
      String sql = "update t_catalog set name = ? where id = ?";
      ps = conn.prepareStatement(sql);
      ps.setString(1, catalog.getName());
      ps.setInt(2, catalog.getId());
      return ps.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtils.release(ps, conn);
    }
    return 0;
  }

  public int deleteById(int id) {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
      conn = JDBCUtils.getConnection();
      String sql = "delete from t_catalog where id = ?";
      ps = conn.prepareStatement(sql);
      ps.setInt(1, id);
      return ps.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtils.release(ps, conn);
    }
    return 0;
  }
}
